﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MODEL;
using MySql.Data.MySqlClient;
using Dapper;

namespace DAL
{
    public class AreaDAL
    {
        public static Task<IEnumerable<Province>> GetAllProvinceList()
        {
            string sql = @"SELECT id
                        , provinceName
                        FROM msap_db.area_province";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.QueryAsync<Province>(sql);
            }
        }

        public static Task<IEnumerable<City>> GetAllCityList()
        {
            string sql = @"SELECT cityid
                            ,cityName
                            ,provinceId 
                            FROM msap_db.area_city";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.QueryAsync<City>(sql);
            }
        }

        public static Task<IEnumerable<County>> GetAllCountyList()
        {
            string sql = @"SELECT countyId
                            ,countyName
                            ,cityId
                            ,provinceId
                            FROM msap_db.area_county";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.QueryAsync<County>(sql);
            }
        }

        public static Task<IEnumerable<Town>> GetTownList()
        {
            string sql = @"SELECT TownId
                            ,TownName                       
                            FROM msap_db.area_town";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.QueryAsync<Town>(sql);
            }
        }
    }
}
